package repositories;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.List;
import java.util.UUID;
import java.util.Vector;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

import domain.Author;
import domain.AuthorPair;

public class AuthorRepository extends BaseRepository {

	public void updateCoauthorsIDtoAuthorID(UUID authorID, UUID oldID) {
		String allAuthorsName = String
				.format("UPDATE author_coauthors SET AuthorID=\""
						+ authorID.toString() + "\" " + "WHERE AuthorID=\""
						+ oldID.toString() + "\"");
		super.executeUpdate(allAuthorsName);
	}

	public Vector<Author> getCoauthors(UUID author) {
		Vector<Author> authors = new Vector<Author>();
		String allAuthorsName = String
				.format("SELECT CoAuthorID FROM author_coauthors WHERE AuthorID=\""
						+ author.toString() + "\"");

		Connection conn = null;
		try {
			conn = super.getConnection();
			ResultSet results = super.executeQuery(conn, allAuthorsName);
			while (results.next()) {
				authors.add(new Author(UUID.fromString(results
						.getString("CoauthorID"))));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		for (int i = 0; i < authors.size(); i++) {
			String sql = String
					.format("SELECT FullName FROM authors WHERE AuthorID=\""
							+ authors.elementAt(i).getAuthorID().toString()
							+ "\"");
			try {
				ResultSet results = super.executeQuery(conn, sql);
				while (results.next())
					authors.elementAt(i).setFullName(
							results.getString("FullName"));
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		super.closeConnection(conn);

		return authors;
	}

	public Vector<Author> getAuthors() {
		Vector<Author> authors = new Vector<Author>();

		String allAuthorsName = String
				.format("SELECT FullName FROM authors ORDER BY FullName");

		Connection conn = null;
		try {
			conn = super.getConnection();
			ResultSet results = super.executeQuery(conn, allAuthorsName);

			while (results.next()) {
				authors.add(new Author(results.getString("FullName")));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			super.closeConnection(conn);
		}

		String sql = String
				.format("SELECT AuthorID FROM authors ORDER BY FullName");
		try {
			ResultSet results = super.executeQuery(conn, sql);
			for (int i = 0; results.next(); i++)
				authors.elementAt(i).setAuthorID(
						UUID.fromString(results.getString("AuthorID")));
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			super.closeConnection(conn);
		}

		return authors;
	}

	public Vector<Author> getAuthors(String fullName) throws Exception {
		if (fullName == null || fullName.trim() == "")
			throw new Exception("Fullname may not be empty.");

		Vector<Author> result = new Vector<Author>();

		String sql = String.format("SELECT " +
				"  AuthorID, FullName, Email, Affiliation " +
				"FROM " +
				"  authors " +
				"WHERE " +
				"  FullName LIKE '%s';",
				super.cleanString(fullName));

		Connection conn = null;
		try {
			conn = super.getConnection();
			ResultSet results = super.executeQuery(conn, sql);

			while(results.next()) {
				Author author = new Author(
						UUID.fromString(results.getString("AuthorID")), 
						results.getString("FullName"),
						results.getString("Email"),
						results.getString("Affiliation")
						);

				Vector<Author> coAuthors = getCoAuthors(author.getAuthorID());
				if (coAuthors != null && coAuthors.size() > 0)
					for(Author coAuthor : coAuthors)
						author.addCoAuthor(coAuthor);

				result.add(author);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			super.closeConnection(conn);
		}

		return result;
	}

	public Author getAuthor(UUID authorID) throws Exception {
		if (authorID == null)
			throw new Exception("Author id is null.");

		Author result = null;

		String sql = String.format("SELECT " +
				"  AuthorID, FullName, Email, Affiliation " +
				"FROM " +
				"  authors " +
				"WHERE " +
				"  AuthorID = '%s';",
				authorID.toString());

		Connection conn = null;
		try {		
			conn = super.getConnection();
			ResultSet results = super.executeQuery(conn, sql);

			if (results.next()) {
				result = new Author(
						UUID.fromString(results.getString("AuthorID")), 
						results.getString("FullName"),
						results.getString("Email"),
						results.getString("Affiliation")
						);

				Vector<Author> coAuthors = getCoAuthors(authorID);
				if (coAuthors != null && coAuthors.size() > 0)
					for(Author coAuthor : coAuthors)
						result.addCoAuthor(coAuthor);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			super.closeConnection(conn);
		}

		return result;
	}

	private Vector<Author> getCoAuthors(UUID authorID) throws Exception {
		if (authorID == null)
			throw new Exception("Author id is null.");

		Vector<Author> result = new Vector<Author>();

		String sql = String.format("SELECT " +
				"  a.AuthorID AS AuthorID, FullName, Email, Affiliation " +
				"FROM " +
				"  coauthors c LEFT JOIN authors a ON c.CoAuthorID = a.AuthorID " +
				"WHERE " +
				"  c.AuthorID = '%s';",
				authorID.toString());

		Connection conn = null;
		try {		
			conn = super.getConnection();
			ResultSet results = super.executeQuery(conn, sql);

			if (results.next()) {
				if (results.getString("AuthorID") == null)
					System.out.println(String.format("Found null AuthorID for '%s'\n%s\n\n", authorID, sql.replaceAll("\\s+", " ")));
				else
					result.add(new Author(
							UUID.fromString(results.getString("AuthorID")),
							results.getString("FullName"),
							results.getString("Email"),
							results.getString("Affiliation")
							));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			super.closeConnection(conn);
		}

		return result;
	}


	public void saveAuthor(Author entity) throws Exception {
		if (entity == null)
			throw new Exception("Author entity is null.");

		String sql = "";
		if (entity.getAuthorID() == null) {
			entity.setAuthorID(UUID.randomUUID()); 

			sql = String.format("INSERT INTO " +
					"  authors " +
					"  (AuthorID, FullName, FirstName, MiddleName, LastName, Email, Affiliation, Created) " +
					"VALUES " +
					"  ('%s', '%s', '%s', '%s', '%s', '%s', '%s', NOW());",
					entity.getAuthorID(),
					cleanString(entity.getFullName()),
					cleanString(entity.getFirstName()),
					cleanString(entity.getMiddleName()),
					cleanString(entity.getLastName()),
					cleanString(entity.getEmail()),
					cleanString(entity.getAffiliation()));
			super.executeUpdate(sql);

			//entity = getAuthor(authorID);
		} else {
			sql = String.format("UPDATE " + 
					"  authors " +
					"SET " + 
					"  FullName = '%s', FirstName = '%s', MiddleName = '%s', LastName = '%s', Email = '%s', Affiliation = '%s' " +
					"WHERE " +
					"  AuthorID = '%s';",
					cleanString(entity.getFullName()),
					cleanString(entity.getFirstName()),
					cleanString(entity.getMiddleName()),
					cleanString(entity.getLastName()),
					cleanString(entity.getEmail()),
					cleanString(entity.getAffiliation()),
					entity.getAuthorID());
			super.executeUpdate(sql);
		}
	}

	public void mergeAuthorIdentifiers(UUID newAuthorID, List<UUID> oldAuthorIDs) throws Exception {
		if (newAuthorID == null)
			throw new Exception("newAuthorID may not be null.");		

		Connection conn = null;
		try {
			conn = super.getConnection();

			Statement stmt = conn.createStatement();

			stmt.addBatch("BEGIN;");

			stmt.addBatch(String.format(
					"REPLACE INTO " +
							"  coauthors " + 
							"  (AuthorID, CoAuthorID, AuthorName, CoAuthorName, Merged) " +
							"SELECT " +
							"  '%s', CoAuthorID, AuthorName, CoAuthorName, 1 " +
							"FROM " +
							"  coauthors " +
							"WHERE " +
							"  AuthorID IN %s",
							newAuthorID.toString(), authorIdsToString(oldAuthorIDs)));

			stmt.addBatch(String.format(
					"REPLACE INTO " +
							"  coauthors " + 
							"  (AuthorID, CoAuthorID, AuthorName, CoAuthorName, Merged) " +
							"SELECT " +
							"  AuthorID, '%s', AuthorName, CoAuthorName, 1 " +
							"FROM " +
							"  coauthors " +
							"WHERE " +
							"  CoAuthorID IN %s;",
							newAuthorID.toString(), authorIdsToString(oldAuthorIDs)));

			stmt.addBatch(String.format(
					"UPDATE " +
							"  paper_authors " + 
							"SET " +
							"  authorID = '%s' " +
							"WHERE " +
							"  authorID IN %s;",
							newAuthorID.toString(), authorIdsToString(oldAuthorIDs)));

			stmt.addBatch(String.format(
					"DELETE FROM " +
							"  authors " + 
							"WHERE " +
							"  authorID IN %s;",
							authorIdsToString(oldAuthorIDs)));

			stmt.addBatch("COMMIT;");
			int[] results = stmt.executeBatch();

			System.out.println(String.format("  Merged: %d author(s),  %d coauthor(s), %d paper author(s) - Deleted: %d authors", results[1], results[2], results[3], results[4]));

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			super.closeConnection(conn);
		}

	}

	private String authorIdsToString(List<UUID> authorIDs) {
		Vector<String> authorIdStrings = new Vector<String>();

		for(UUID authorID : authorIDs) {
			authorIdStrings.add(String.format("'%s'", authorID));
		}

		return authorIdStrings.toString().replace("[", "(").replace("]", ")");
	}

	/*
	public Vector<AuthorPair> listAuthorPairs(String seed) {
		Vector<AuthorPair> authorPairs = new Vector<AuthorPair>();

		String sql = String.format("SELECT " + 
				"  AuthorName, CoAuthorName "+
				"FROM " +
				"  coauthors " +
				"WHERE " +
				"  AuthorName = '%s' " +
				"GROUP BY " + 
				"  AuthorName, CoAuthorName " + 
				"HAVING " +
				"  COUNT(*) > 1 " +
				"ORDER BY COUNT(*) DESC;",
				super.cleanString(seed));

		Connection conn = null;
		try {
			conn = super.getConnection();
			ResultSet results = super.executeQuery(conn, sql);

			while (results.next()) {
				authorPairs.add(new AuthorPair(results.getString("AuthorName"), results.getString("CoAuthorName")));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			super.closeConnection(conn);
		}

		return authorPairs;
	}
	 */
	public List<Author> listAuthors(int startIndex, int maxRecords) {
		Vector<Author> authors = new Vector<Author>();

		String sql = String.format("SELECT  "
				+ "  AuthorID, FullName, Email, Affiliation " + "FROM "
				+ "  authors " + "ORDER BY Created " + "LIMIT %d, %d;",
				startIndex, maxRecords);

		Connection conn = null;

		try {
			conn = super.getConnection();
			ResultSet results = super.executeQuery(conn, sql);

			while (results.next()) {
				authors.add(new Author(UUID.fromString(results
						.getString("AuthorID")), results.getString("FullName"),
						results.getString("Email"), results
						.getString("Affiliation")));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			super.closeConnection(conn);
		}
		return Collections.unmodifiableList(authors);
	}

	public String[] getHighestOrderPair() {
		String[] result = null;

		String sql = "SELECT " + 
				"  AuthorName, CoAuthorName " +
				"FROM " +
				"  coauthors " +
				"GROUP BY " +
				"  AuthorName, CoAuthorName " +
				"HAVING " + 
				"  COUNT(*) > 1 " +
				"ORDER BY " +
				"  COUNT(*) DESC " +
				"LIMIT 1;";

		Connection conn = null;
		try {
			conn = super.getConnection();
			ResultSet results = super.executeQuery(conn, sql);

			if (results.next()) {
				result = new String[] { results.getString("AuthorName"), results.getString("CoAuthorName") };
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			super.closeConnection(conn);
		}

		return result;
	}

	public Vector<UUID> getAuthorIdentifiersForCoAuthors(String authorName, String coAuthorName) {
		Vector<UUID> result = new Vector<UUID>();

		String sql = String.format("SELECT DISTINCT " +
				"  AuthorID " +
				"FROM " +
				"  coauthors " +
				"WHERE " +
				"  AuthorName = '%s' AND CoAuthorName = '%s';",
				super.cleanString(authorName), super.cleanString(coAuthorName));

		Connection conn = null;
		try {
			conn = super.getConnection();
			ResultSet results = super.executeQuery(conn, sql);

			while (results.next()) {
				result.add(UUID.fromString(results.getString("AuthorID")));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			super.closeConnection(conn);
		}

		return result;
	}
}